package cn.agiledata.bank.transaction.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.agiledata.bank.common.util.Constant;
import cn.agiledata.bank.common.util.DateUtil;
import cn.agiledata.bank.common.util.StringUtil;
import cn.agiledata.bank.hibernateBean.Account;
import cn.agiledata.bank.transaction.logic.BatchTransSummary;
import cn.agiledata.bank.transaction.logic.EsPrePayTransBean;

import com.vnv.infrastructure.logic.IPBHibernatePagingSupport;
import com.vnv.infrastructure.util.PagingResult;

public class BatchTransSummaryDaoImpl extends IPBHibernatePagingSupport
        implements BatchTransSummaryDao {

    public PagingResult getBatchTransSummaryListWithoutCancel(
            EsPrePayTransBean bean, int pageNo, int pageSize) {
        String hql = "select new "
                + BatchTransSummary.class.getName()
                + "(t.batchNo,t.debit,t.actionType,max(t.beginDate),sum(t.amount),sum(t.debitFee),count(t),t.transStatus) "
                + "from EsPrePayTrans t where t.batchNo is not null and t.transStatus = :transStatus "
                + "and 0=(select count(e) from EsPrePayTrans e where e.batchNo=t.batchNo "
                + "and e.transStatus<>:transStatus_cancel and e.transStatus<>:transStatus)";
        Map params = new HashMap();
        params.put("transStatus_cancel", Constant.ES_TRANS_STATUS_CANCEL);
        params.put("transStatus", bean.getTransStatus());
        if (bean.getSubAccounts() != null && bean.getSubAccounts().size() > 0) {
            hql += " and t.debit in (:subAccountList)";
            params.put("subAccountList", bean.getSubAccounts());
        }
        else {
        	// zln 修正 20080428 子账户列表为空表示无支付权限，不检索出任何数据
        	// FIXME 此处应该不查询出任何数据，可以考虑优化此处逻辑。
        	hql += " and t.debit=-99";
        }
        if (!StringUtil.isStrEmpty(bean.getBatchNo())) {
            hql += " and t.batchNo like :batchNo";
            params.put("batchNo", "%" + bean.getBatchNo() + "%");
        }
        if (bean.getBeginDate() != null) {
            hql += " and t.beginDate >= :beginDate";
            params.put("beginDate", bean.getBeginDate());
        }
        if (bean.getEndDate() != null) {
            hql += " and t.beginDate <= :endDate";
            params.put("endDate", DateUtil.latestOfDate(bean.getEndDate()));
        }
        if (!StringUtil.isStrEmpty(bean.getActionTypeID())) {
            hql += " and t.actionType.id = :actionType";
            params.put("actionType", new Long(bean.getActionTypeID()));
        }
        if (!StringUtil.isStrEmpty(bean.getSubAccountID())) {
            hql += " and t.debit.id = :subAccountId";
            params.put("subAccountId", new Long(bean.getSubAccountID()));
        }
        if (!StringUtil.isStrEmpty(bean.getCreateUser())) {
            hql += " and t.operatorSeq = :createUser";
            params.put("createUser", bean.getCreateUser());
        }
        hql += " group by t.batchNo,t.debit,t.actionType,t.transStatus";
        hql += " having count(t)>0";
        if (!StringUtil.isStrEmpty(bean.getAmountMax())) {
            hql += " and sum(t.amount) + sum(t.debitFee) <= :amountMax";
            params.put("amountMax", new Double(bean.getAmountMax()));
        }
        if (!StringUtil.isStrEmpty(bean.getAmountMin())) {
            hql += " and sum(t.amount) + sum(t.debitFee) >= :amountMin";
            params.put("amountMin", new Double(bean.getAmountMin()));
        }
        if(!StringUtil.isStrEmpty(bean.getBeginCount())){
        	hql += " and count(t) >= :beginCount";
        	params.put("beginCount", new Integer(bean.getBeginCount()));
        }
        if(!StringUtil.isStrEmpty(bean.getEndCount())){
        	hql += " and count(t) <= :endCount";
        	params.put("endCount", new Integer(bean.getEndCount()));
        }
        hql += " order by t.batchNo desc";
        return findByHqlAndNamedParam(hql, params, pageNo, pageSize);
    }

    public BatchTransSummary getBatchTransSummaryTotal(String batchNo) {
        String hql = "select new "
                + BatchTransSummary.class.getName()
                + "(t.batchNo,t.debit,t.actionType,max(t.beginDate),sum(t.amount),sum(t.debitFee),count(t)) "
                + "from EsPrePayTrans t where t.batchNo = ? "
                + "group by t.batchNo,t.debit,t.actionType order by t.batchNo desc";
        List resultList = getHibernateTemplate().find(hql, batchNo);
        return resultList.size() == 0 ? null : (BatchTransSummary) resultList
                .get(0);
    }

    public BatchTransSummary[] getBatchTransSummaryTotalList(List batchNoList) {
        if (batchNoList.size() == 0) {
            return new BatchTransSummary[0];
        }
        String hql = "select new "
                + BatchTransSummary.class.getName()
                + "(t.batchNo,t.debit,t.actionType,max(t.beginDate),sum(t.amount),sum(t.debitFee),count(t)) "
                + "from EsPrePayTrans t where t.batchNo in (:batchNoList) "
                + "group by t.batchNo,t.debit,t.actionType order by t.batchNo desc";
        List resultList = getHibernateTemplate().findByNamedParam(hql,
                "batchNoList", batchNoList);
        return (BatchTransSummary[]) resultList
                .toArray(new BatchTransSummary[0]);
    }

    public BatchTransSummary getBatchTransSummaryByTransStatus(String batchNo,
            String transStatus) {
        String hql = "select new "
                + BatchTransSummary.class.getName()
                + "(t.batchNo,t.debit,t.actionType,max(t.beginDate),sum(t.amount),sum(t.debitFee),count(t),t.transStatus) "
                + "from EsPrePayTrans t where t.batchNo=? and t.transStatus=?"
                + "group by t.batchNo,t.debit,t.actionType,t.transStatus";
        List resultList = getHibernateTemplate().find(hql,
                new Object[] { batchNo, transStatus });
        return resultList.size() == 0 ? null : (BatchTransSummary) resultList
                .get(0);
    }

    public BatchTransSummary[] getBatchTransSummaryListGroupByTransStatus(
            String batchNo) {
        String hql = "select new "
                + BatchTransSummary.class.getName()
                + "(t.batchNo,t.debit,t.actionType,max(t.beginDate),sum(t.amount),sum(t.debitFee),count(t),t.transStatus) "
                + "from EsPrePayTrans t where t.batchNo = ? "
                + "group by t.batchNo,t.debit,t.actionType,t.transStatus";
        List resultList = getHibernateTemplate().find(hql, batchNo);
        return (BatchTransSummary[]) resultList
                .toArray(new BatchTransSummary[0]);
    }

    public BatchTransSummary[] getBatchTransSummaryListOnlyBatchNo(Account debit,
            String transStatus) {
        String hql = "select new "
                + BatchTransSummary.class.getName()
                + "(t.batchNo) "
                + "from EsPrePayTrans t where t.debit.account=? and t.transStatus=? "
                + "group by t.batchNo order by t.batchNo desc";
        List resultList = getHibernateTemplate().find(hql,
                new Object[] { debit, transStatus });
        return (BatchTransSummary[]) resultList
                .toArray(new BatchTransSummary[0]);
    }

	public PagingResult getBatchAuditingTransSummaryList(EsPrePayTransBean bean, int pageNo, int pageSize) {
		  String hql = "select new "
              + BatchTransSummary.class.getName()
              + "(t.batchNo,t.debit,t.actionType,max(t.beginDate),sum(t.amount),sum(t.debitFee),count(t),t.transStatus) "
              + "from EsPrePayTrans t where t.batchNo is not null and t.transStatus = :transStatus "
		      + "and 0<>(select count(e) from EsPrePayTrans e where e.batchNo=t.batchNo "
		      + "and e.transStatus<>:transStatus_cancel )";
      Map params = new HashMap();
      params.put("transStatus_cancel", Constant.ES_TRANS_STATUS_CANCEL);
      params.put("transStatus", bean.getTransStatus());
      if (bean.getSubAccounts() != null && bean.getSubAccounts().size() > 0) {
          hql += " and t.debit in (:subAccountList)";
          params.put("subAccountList", bean.getSubAccounts());
      }
      if (!StringUtil.isStrEmpty(bean.getBatchNo())) {
          hql += " and t.batchNo like :batchNo";
          params.put("batchNo", "%" + bean.getBatchNo() + "%");
      }
      if (bean.getBeginDate() != null) {
          hql += " and t.beginDate >= :beginDate";
          params.put("beginDate", bean.getBeginDate());
      }
      if (bean.getEndDate() != null) {
          hql += " and t.beginDate <= :endDate";
          params.put("endDate", DateUtil.latestOfDate(bean.getEndDate()));
      }
      if (!StringUtil.isStrEmpty(bean.getActionTypeID())) {
          hql += " and t.actionType.id = :actionType";
          params.put("actionType", new Long(bean.getActionTypeID()));
      }
      if (!StringUtil.isStrEmpty(bean.getSubAccountID())) {
          hql += " and t.debit.id = :subAccountId";
          params.put("subAccountId", new Long(bean.getSubAccountID()));
      }
      if (!StringUtil.isStrEmpty(bean.getCreateUser())) {
          hql += " and t.operatorSeq = :createUser";
          params.put("createUser", bean.getCreateUser());
      }
      hql += " group by t.batchNo,t.debit,t.actionType,t.transStatus";
      hql += " having count(t)>0";
      if (!StringUtil.isStrEmpty(bean.getAmountMax())) {
          hql += " and sum(t.amount) + sum(t.debitFee) <= :amountMax";
          params.put("amountMax", new Double(bean.getAmountMax()));
      }
      if (!StringUtil.isStrEmpty(bean.getAmountMin())) {
          hql += " and sum(t.amount) + sum(t.debitFee) >= :amountMin";
          params.put("amountMin", new Double(bean.getAmountMin()));
      }
      if(!StringUtil.isStrEmpty(bean.getBeginCount())){
      	hql += " and count(t) >= :beginCount";
      	params.put("beginCount", new Long(bean.getBeginCount()));
      }
      if(!StringUtil.isStrEmpty(bean.getEndCount())){
      	hql += " and count(t) <= :endCount";
      	params.put("endCount", new Long(bean.getEndCount()));
      }
      hql += " order by t.batchNo desc";
      return findByHqlAndNamedParam(hql, params, pageNo, pageSize);
	}
	
	public PagingResult getBatchAuditeRejectTransSummaryList(EsPrePayTransBean bean, int pageNo, int pageSize) {
		  String hql = "select new "
            + BatchTransSummary.class.getName()
            + "(t.batchNo,t.debit,t.actionType,max(t.beginDate),sum(t.amount),sum(t.debitFee),count(t),t.transStatus) "
            + "from EsPrePayTrans t where t.batchNo is not null and t.transStatus = :transStatus and t.createUser.id = :createUserId"
		      + "and 0<>(select count(e) from EsPrePayTrans e where e.batchNo=t.batchNo "
		      + "and e.transStatus<>:transStatus_cancel )";
    Map params = new HashMap();
    params.put("transStatus_cancel", Constant.ES_TRANS_STATUS_CANCEL);
    params.put("transStatus", bean.getTransStatus());
    params.put("createUserId", bean.getUpdateUserId());
    if (bean.getSubAccounts() != null && bean.getSubAccounts().size() > 0) {
        hql += " and t.debit in (:subAccountList)";
        params.put("subAccountList", bean.getSubAccounts());
    }
    if (!StringUtil.isStrEmpty(bean.getBatchNo())) {
        hql += " and t.batchNo like :batchNo";
        params.put("batchNo", "%" + bean.getBatchNo() + "%");
    }
    if (bean.getBeginDate() != null) {
        hql += " and t.beginDate >= :beginDate";
        params.put("beginDate", bean.getBeginDate());
    }
    if (bean.getEndDate() != null) {
        hql += " and t.beginDate <= :endDate";
        params.put("endDate", DateUtil.latestOfDate(bean.getEndDate()));
    }
    if (!StringUtil.isStrEmpty(bean.getActionTypeID())) {
        hql += " and t.actionType.id = :actionType";
        params.put("actionType", new Long(bean.getActionTypeID()));
    }
    if (!StringUtil.isStrEmpty(bean.getSubAccountID())) {
        hql += " and t.debit.id = :subAccountId";
        params.put("subAccountId", new Long(bean.getSubAccountID()));
    }
    if (!StringUtil.isStrEmpty(bean.getCreateUser())) {
        hql += " and t.operatorSeq = :createUser";
        params.put("createUser", bean.getCreateUser());
    }
    hql += " group by t.batchNo,t.debit,t.actionType,t.transStatus";
    hql += " having count(t)>0";
    if (!StringUtil.isStrEmpty(bean.getAmountMax())) {
        hql += " and sum(t.amount) + sum(t.debitFee) <= :amountMax";
        params.put("amountMax", new Double(bean.getAmountMax()));
    }
    if (!StringUtil.isStrEmpty(bean.getAmountMin())) {
        hql += " and sum(t.amount) + sum(t.debitFee) >= :amountMin";
        params.put("amountMin", new Double(bean.getAmountMin()));
    }
    if(!StringUtil.isStrEmpty(bean.getBeginCount())){
    	hql += " and count(t) >= :beginCount";
    	params.put("beginCount", new Long(bean.getBeginCount()));
    }
    if(!StringUtil.isStrEmpty(bean.getEndCount())){
    	hql += " and count(t) <= :endCount";
    	params.put("endCount", new Long(bean.getEndCount()));
    }
    hql += " order by t.batchNo desc";
    return findByHqlAndNamedParam(hql, params, pageNo, pageSize);
	}

}
